package cn.edu.zzuli.web.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;

import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Repository;

import cn.edu.zzuli.web.bean.User;
import cn.edu.zzuli.web.bean.rowmapper.UserRowMapper;
import cn.edu.zzuli.web.dao.base.AbstractDao;

/**
 * 用户表数据库操作
 * @author caohua
 */
@Repository("userDAO")
public class UserDAO extends AbstractDao {

	public UserDAO() {
	}

	/**
	 * 查询用户总数 （分页方法）
	 */
	public int getTotalUser() {
		StringBuffer sql = new StringBuffer("select count(1) from user");
		return this.getJdbcTemplate().queryForInt(sql.toString());
	}

	/**
	 * 查询当前页用户 （分页方法）
	 */
	public List<User> getUserByPage(int pageSize, int currentPage) {
		StringBuffer sql = new StringBuffer(
				"select id,username,nickname,password,sex,birthday,major,address,"
						+ "introduces,enabled from user limit ?,?");
		return this.getJdbcTemplate().query(sql.toString(),
				new Object[] { pageSize * (currentPage - 1), pageSize },
				new UserRowMapper());
	}

	/**
	 * 插入用户
	 */
	public int insertUser(User user) {
		String sql = "insert into user (username,nickname,password,sex,birthday,major,address,introduces,enabled) "
				+ "values(?, ?, ?, ?, ?, ?, ?, ?, ?)";
		Object[] params = new Object[] { user.getUsername(),
				user.getNickname(), user.getPassword(), user.getSex(),
				user.getBirthday(), user.getMajor(), user.getAddress(),
				user.getIntroduces(), user.getEnabled() };
		return this.getJdbcTemplate().update(sql, params);
	}

	/**  
	 * 插入用户 
	 */ 
	public int insertUserWithType(User user) {
		String sql = "insert into user (username,nickname,password,sex,birthday,major,address,introduces,enabled) "
				+ "values(?, ?, ?, ?, ?, ?, ?, ?, ?)";
		Object[] params = new Object[] { user.getUsername(),
				user.getNickname(), user.getPassword(), user.getSex(),
				user.getBirthday(), user.getMajor(), user.getAddress(),
				user.getIntroduces(), user.getEnabled() };
		int[] types = new int[] { Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
				Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
				Types.VARCHAR, Types.INTEGER };
		return this.getJdbcTemplate().update(sql, params, types);
	}

	/**
	 * 根据用户名得到用户
	 */
	public User getUserByUsername(String username,String password) {
		String sql = "select * from user where username = ? and password = ?";
		final User ret = new User();
		final Object params[] = new Object[] { username ,password };
		this.getJdbcTemplate().query(sql, params, new RowCallbackHandler() {
			public void processRow(ResultSet rs) throws SQLException {
				ret.setId(rs.getInt("id"));
				ret.setUsername(rs.getString("username"));
				ret.setNickname(rs.getString("nickname"));
				ret.setPassword(rs.getString("password"));
				ret.setSex(rs.getString("sex"));
				ret.setBirthday(rs.getString("birthday"));
				ret.setMajor(rs.getString("major"));
				ret.setAddress(rs.getString("address"));
				ret.setIntroduces(rs.getString("introduces"));
				ret.setEnabled(rs.getInt("enabled"));
			}
		});
		return ret;
	}

	/**
	 * 更具id得到用户
	 */
	public User getUserById(int id) {
		String sql = "select * from user where id = ?";
		final User ret = new User();
		final Object params[] = new Object[] { id };
		this.getJdbcTemplate().query(sql, params, new RowCallbackHandler() {
			public void processRow(ResultSet rs) throws SQLException {
				ret.setId(rs.getInt("id"));
				ret.setUsername(rs.getString("username"));
				ret.setNickname(rs.getString("nickname"));
				ret.setPassword(rs.getString("password"));
				ret.setSex(rs.getString("sex"));
				ret.setBirthday(rs.getString("birthday"));
				ret.setMajor(rs.getString("major"));
				ret.setAddress(rs.getString("address"));
				ret.setIntroduces(rs.getString("introduces"));
				ret.setEnabled(rs.getInt("enabled"));
			}
		});
		return ret;
	}

	/**
	 * 停用用户
	 */
	public void disableUserById(int id) {
		String sql = "update user set enabled = 1 where id = ?";
		Object[] params = new Object[] { id };
		this.getJdbcTemplate().update(sql, params);
	}

	/**
	 * 更新用户
	 */
	public int updateUser(User user) {
		String sql = "update user set username=?,nickname=?,sex=?,birthday=?,major=?,address=?"
				+ ",introduces=?,enabled=? where id = ?";
		Object[] params = new Object[] { user.getUsername(),
				user.getNickname(), user.getSex(),
				user.getBirthday(), user.getMajor(), user.getAddress(),
				user.getIntroduces(),user.getEnabled(), user.getId() };
		return this.getJdbcTemplate().update(sql, params);
	}
	
	/**
	 * 更新用户密码
	 */
	public void updatePassword(String password ,int id) {
		String sql = "update user set password = ? where id = ?";
		Object[] params = new Object[] { password, id };
		this.getJdbcTemplate().update(sql, params);
	}

	public void insertUserSecurity(User userSecurity, int roleId) {
		String sql = "insert into user_role (uid,rid) values (?,?)";
		Object[] params = new Object[] { userSecurity.getId() , roleId};
		this.getJdbcTemplate().update(sql, params);
	}
	
	public int getUserSecurity(int id) {
		String sql = "select rid from user_role where uid = ?";
		Object[] params = new Object[] { id };
		return this.getJdbcTemplate().queryForInt(sql, params);
	}
	
	public void updateUserSecurity(User userSecurity, int roleId) {
		String sql = "update user_role set rid = ? where uid = ?";
		Object[] params = new Object[] { roleId , userSecurity.getId()};
		this.getJdbcTemplate().update(sql, params);
	}

}